from __future__ import annotations

from typing import Dict, List, Optional, Sequence, Tuple

from sqlalchemy import Select, func, or_, select, update
from sqlalchemy.ext.asyncio import AsyncSession

from app.crud.base import CRUDBase
from app.models.dictionary import DictType, DictData
from app.schemas.dictionary import (
    DictTypeCreate,
    DictTypeUpdate,
    DictDataCreate,
    DictDataUpdate,
)


class CRUDDictType(CRUDBase[DictType, DictTypeCreate, DictTypeUpdate]):
    async def get_by_dict_type(
        self,
        db: AsyncSession,
        *,
        dict_type: str,
    ) -> Optional[DictType]:
        stmt = select(DictType).where(DictType.dict_type == dict_type)
        result = await db.execute(stmt)
        return result.scalars().first()

    async def get_multi_with_filters(
        self,
        db: AsyncSession,
        *,
        keyword: Optional[str] = None,
        status: Optional[str] = None,
        skip: int = 0,
        limit: int = 10,
    ) -> Tuple[List[DictType], int]:
        filters = []
        if keyword:
            pattern = f"%{keyword.strip()}%"
            filters.append(
                or_(
                    DictType.dict_name.ilike(pattern),
                    DictType.dict_type.ilike(pattern),
                )
            )
        if status is not None:
            filters.append(DictType.status == status)

        stmt: Select = select(DictType)
        count_stmt: Select = select(func.count(DictType.id))

        if filters:
            stmt = stmt.where(*filters)
            count_stmt = count_stmt.where(*filters)

        stmt = stmt.order_by(DictType.created_at.desc()).offset(skip).limit(limit)

        total = (await db.execute(count_stmt)).scalar_one()
        items = (await db.execute(stmt)).scalars().all()
        return items, total

    async def create_with_operator(
        self,
        db: AsyncSession,
        *,
        obj_in: DictTypeCreate,
        operator: Optional[str] = None,
    ) -> DictType:
        payload = obj_in.model_dump()
        payload.update(
            {
                "create_by": operator,
                "update_by": operator,
            }
        )
        db_obj = DictType(**payload)
        db.add(db_obj)
        await db.flush()
        await db.refresh(db_obj)
        return db_obj

    async def update_with_operator(
        self,
        db: AsyncSession,
        *,
        db_obj: DictType,
        obj_in: DictTypeUpdate,
        operator: Optional[str] = None,
    ) -> DictType:
        update_data = obj_in.model_dump(exclude_unset=True)
        if operator is not None:
            update_data["update_by"] = operator

        return await super().update(db, db_obj=db_obj, obj_in=update_data)


class CRUDDictData(CRUDBase[DictData, DictDataCreate, DictDataUpdate]):
    async def get_multi_with_filters(
        self,
        db: AsyncSession,
        *,
        dict_type: Optional[str] = None,
        keyword: Optional[str] = None,
        status: Optional[str] = None,
        skip: int = 0,
        limit: int = 10,
    ) -> Tuple[List[DictData], int]:
        filters = []
        if dict_type:
            filters.append(DictData.dict_type == dict_type)
        if keyword:
            pattern = f"%{keyword.strip()}%"
            filters.append(
                or_(
                    DictData.dict_label.ilike(pattern),
                    DictData.dict_value.ilike(pattern),
                )
            )
        if status is not None:
            filters.append(DictData.status == status)

        stmt: Select = select(DictData)
        count_stmt: Select = select(func.count(DictData.id))

        if filters:
            stmt = stmt.where(*filters)
            count_stmt = count_stmt.where(*filters)

        stmt = stmt.order_by(DictData.dict_sort.asc(), DictData.created_at.desc()).offset(skip).limit(limit)

        total = (await db.execute(count_stmt)).scalar_one()
        items = (await db.execute(stmt)).scalars().all()
        return items, total

    async def get_items_by_type(
        self,
        db: AsyncSession,
        *,
        dict_type: str,
        status: Optional[str] = None,
    ) -> List[DictData]:
        stmt = select(DictData).where(DictData.dict_type == dict_type)
        if status is not None:
            stmt = stmt.where(DictData.status == status)
        stmt = stmt.order_by(DictData.dict_sort.asc(), DictData.created_at.desc())
        result = await db.execute(stmt)
        return result.scalars().all()

    async def get_batch_by_types(
        self,
        db: AsyncSession,
        *,
        types: Sequence[str],
        status: Optional[str] = None,
    ) -> Dict[str, List[DictData]]:
        if not types:
            return {}

        stmt = select(DictData).where(DictData.dict_type.in_(types))
        if status is not None:
            stmt = stmt.where(DictData.status == status)
        stmt = stmt.order_by(DictData.dict_type.asc(), DictData.dict_sort.asc(), DictData.created_at.desc())

        result = await db.execute(stmt)
        records = result.scalars().all()

        grouped: Dict[str, List[DictData]] = {}
        for record in records:
            grouped.setdefault(record.dict_type, []).append(record)
        return grouped

    async def get_by_value(
        self,
        db: AsyncSession,
        *,
        dict_type: str,
        dict_value: str,
        exclude_id: Optional[str] = None,
    ) -> Optional[DictData]:
        stmt = select(DictData).where(
            DictData.dict_type == dict_type,
            DictData.dict_value == dict_value,
        )
        if exclude_id is not None:
            stmt = stmt.where(DictData.id != exclude_id)
        result = await db.execute(stmt)
        return result.scalars().first()

    async def clear_default(
        self,
        db: AsyncSession,
        *,
        dict_type: str,
        exclude_id: Optional[str] = None,
    ) -> None:
        stmt = update(DictData).where(
            DictData.dict_type == dict_type,
            DictData.is_default == "Y",
        )
        if exclude_id is not None:
            stmt = stmt.where(DictData.id != exclude_id)
        await db.execute(stmt.values(is_default="N"))

    async def create_with_type(
        self,
        db: AsyncSession,
        *,
        dict_type: DictType,
        obj_in: DictDataCreate,
        operator: Optional[str] = None,
    ) -> DictData:
        payload = obj_in.model_dump()
        payload.update(
            {
                "dict_type": dict_type.dict_type,
                "dict_type_id": dict_type.id,
                "create_by": operator,
                "update_by": operator,
            }
        )

        if payload.get("is_default") == "Y":
            await self.clear_default(db, dict_type=dict_type.dict_type)

        db_obj = DictData(**payload)
        db.add(db_obj)
        await db.flush()
        await db.refresh(db_obj)
        return db_obj

    async def update_with_default(
        self,
        db: AsyncSession,
        *,
        db_obj: DictData,
        obj_in: DictDataUpdate,
        operator: Optional[str] = None,
    ) -> DictData:
        update_data = obj_in.model_dump(exclude_unset=True)
        if operator is not None:
            update_data["update_by"] = operator

        if update_data.get("is_default") == "Y":
            await self.clear_default(db, dict_type=db_obj.dict_type, exclude_id=db_obj.id)

        return await super().update(db, db_obj=db_obj, obj_in=update_data)


dict_type = CRUDDictType(DictType)
dict_data = CRUDDictData(DictData)
